/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package codigoJava;
import java.sql.*;
import java.util.ArrayList;
import java.util.Map;

import java.util.UUID;
import javax.sql.DataSource;
/**
 *
 * @author CACEDA
 */
public class GestorBD {
DataSource ds;

    public GestorBD() {
        this.ds = null;
    }

    public GestorBD(DataSource ds) {
        this.ds = ds;
    } //Constructor

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    public Connection getConnection() throws SQLException {

        Connection conn = ds.getConnection();

        return conn;

        //return ds.getConnection();
    }

    public Integer getID(String tabla, String condicion) {

        int id = 0;
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            ResultSet rset = stmt.executeQuery("SELECT id" + tabla + " FROM `conPalomitas`.`" + tabla + "` WHERE " + condicion);

            if (rset.next()) {
                id = rset.getInt("id" + tabla);
            }


            if (rset != null) {
                rset.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

        } catch (Exception e) {
        } finally {
            return id;
        }
    }

    public void borrarFila(String tabla, String condicion) {
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            stmt.executeUpdate("DELETE FROM `conPalomitas`.`" + tabla + "` WHERE " + condicion);

            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

        } catch (Exception e) {
        }
    }

    public boolean guardar(ObjetoBD o) {
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            String sentencia = "INSERT INTO `conPalomitas`.`" + o.getTabla() + "` (";
            String values = ") VALUES (";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += "`" + entry.getKey() + "`";
                    values += "'" + entry.getValue() + "'";
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += ",`" + entry.getKey() + "`";
                    values += ",'" + entry.getValue() + "'";
                }
            }
            sentencia += values + ");";

            stmt.executeUpdate(sentencia);

            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

            return true;

        } catch (Exception e) {
            System.out.println(e);
            return false;
        }
    }

    public boolean actualizar(ObjetoBD o) {
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            String sentencia = "UPDATE `conPalomitas`.`" + o.getTabla() + "` SET ";
            String where = " WHERE ";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += entry.getKey() + "='" + entry.getValue() + "'";
                    where += "ID=" + o.getId();
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += "," + entry.getKey() + "='" + entry.getValue() + "'";
                }
            }
            sentencia += where;
            stmt.executeUpdate(sentencia);

            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

            return true;

        } catch (Exception e) {

            return false;
        }
    }

    public boolean borrar(ObjetoBD o) {
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            String sentencia = "DELETE FROM `conPalomitas`.`" + o.getTabla() + "` WHERE ";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += entry.getKey() + "='" + entry.getValue() + "'";
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += " AND " + entry.getKey() + "='" + entry.getValue() + "'";
                }
            }
            stmt.executeUpdate(sentencia);

            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

            return true;

        } catch (Exception e) {

            return false;
        }
    }

    public boolean borrarPorCondicion(ObjetoBD o, String condicion) {
        try {
            Connection conn = this.getConnection();
            Statement stmt = conn.createStatement();

            String sentencia = "DELETE FROM `conPalomitas`.`" + o.getTabla() + "` WHERE " + condicion;
            stmt.executeUpdate(sentencia);

            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }

            return true;

        } catch (Exception e) {

            return false;
        }
    }

    public Producto[] consultarArticulos(String condicion) throws SQLException {

        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        ArrayList auxList = new ArrayList();
        ResultSet rset = stmt.executeQuery("SELECT * FROM `conPalomitas`.`producto` WHERE " + condicion);
        while (rset.next()) {

            Producto aux = new Producto();
            aux.setNombre(rset.getString("nombre"));
            aux.setDescripcion(rset.getString("descripcion"));
            aux.setCategoria(rset.getString("categoria"));
            aux.setPrecio(rset.getFloat("precio"));
            aux.setImagen(rset.getString("imagen"));
            aux.setUnidades(rset.getInt("unidades"));
            auxList.add(aux);

        }
        Producto[] a = new Producto[auxList.size()];
        for (int i = 0; i < a.length; i++) {
            a[i] = (Producto) auxList.get(i);

        }


        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
        return a;
    }

    Producto[] listarArticulos() throws SQLException {
        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        ArrayList auxList = new ArrayList();
        ResultSet rset = stmt.executeQuery("SELECT * FROM `conPalomitas`.`producto`");
        while (rset.next()) {

            Producto aux = new Producto();
            aux.setNombre(rset.getString("nombre"));
            aux.setDescripcion(rset.getString("descripcion"));
            aux.setCategoria(rset.getString("categoria"));
            aux.setPrecio(rset.getFloat("precio"));
            aux.setImagen(rset.getString("imagen"));
            aux.setUnidades(rset.getInt("unidades"));
            auxList.add(aux);

        }
        Producto[] a = new Producto[auxList.size()];
        for (int i = 0; i < a.length; i++) {
            a[i] = (Producto) auxList.get(i);

        }


        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
        return a;
    }

    Usuario buscarUsuarioPorCondicion(String condicion) throws SQLException {
        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("SELECT * FROM `conPalomitas`.`usuario` WHERE " + condicion);


        Usuario aux = new Usuario();
        while (rset.next()) {
            aux.setNombre(rset.getString("nombre"));
            aux.setApellidos(rset.getString("apellidos"));
            aux.setCorreo(rset.getString("correo"));
            aux.setDireccionEnvio(rset.getString("direccionEnvio"));
        }


        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
        return aux;
    }

    public boolean guardarFactura(Factura fac) throws SQLException {

        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        //Long uuid = UUID.randomUUID().getMostSignificantBits();
        Long uuid = fac.getUuid();
        String sentencia = "INSERT INTO `conPalomitas`.`factura` (nombre,apellidos,correo,direccionEnvio,total,cantidad,uuidFactura) values ";
        sentencia = sentencia + "('" + fac.getNombre() + "','" + fac.getApellidos() + "','" + fac.getCorreo() + "','" + fac.getDireccionEnvio() + "'," + fac.getTotal() + "," + fac.getCantidad() + "," + uuid + ")";
        String[] productosComprados = fac.getProductos();
        stmt.executeUpdate(sentencia);

        for (int i = 0; i < productosComprados.length; i++) {
            String articulo = productosComprados[i];
            sentencia = "INSERT INTO `conPalomitas`.`producto_factura` (nombre,uuidFactura) values ";
            sentencia = sentencia + "('" + articulo + "'," + uuid + ")";
            stmt.executeUpdate(sentencia);
        }

        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }

        return true;



    }

    public Factura[] ListarTodasLasFacturas() throws SQLException {
        Connection conn = this.getConnection();
        Connection conn2 = this.getConnection();
        Statement stmt = conn.createStatement();
        Statement stmt2 = conn2.createStatement();
        ArrayList auxList = new ArrayList();
        ResultSet rset = stmt.executeQuery("SELECT * FROM `conPalomitas`.`factura`");
        while (rset.next()) {

            Factura aux = new Factura();
            aux.setNombre(rset.getString("nombre"));
            aux.setApellidos(rset.getString("apellidos"));
            aux.setCorreo(rset.getString("correo"));
            aux.setDireccionEnvio(rset.getString("direccionEnvio"));
            aux.setTotal(rset.getFloat("total"));
            aux.setCantidad(rset.getInt("cantidad"));
            aux.setUuid(rset.getLong("uuidFactura"));
            //AHORA BUSCAR LAS PELICULAS COMPRADAS
            int NumPeliculasFactura = aux.getCantidad();
            String[] productosComprados = new String[NumPeliculasFactura];
            int contador = 0;
            ResultSet rset2 = stmt2.executeQuery("SELECT * FROM `conPalomitas`.`producto_factura` where uuidFactura=" + aux.getUuid());

            while (rset2.next()) {
                if (contador >= NumPeliculasFactura) {
                    break;
                }
                productosComprados[contador] = rset2.getString("nombre");
                contador++;
            }
            if (rset2 != null) {
                rset2.close();
            }
            aux.inicializarArrayProductos(NumPeliculasFactura);//necesario inicializar el array
            aux.setProductos(productosComprados);
            auxList.add(aux);

        }

        Factura[] a = new Factura[auxList.size()];
        for (int i = 0; i < a.length; i++) {
            a[i] = (Factura) auxList.get(i);

        }
        if (rset != null) {
            rset.close();

        }
        if (stmt != null) {
            stmt.close();
            stmt2.close();
        }
        if (conn != null) {
            conn.close();
            conn2.close();
        }
        return a;
    }

    void modificarCantidadArticulo(int cantidad, String nombre) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    public boolean modificarProducto(Producto pro) throws SQLException {
        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        String sentencia = "UPDATE producto SET descripcion='" + pro.getDescripcion() + "', categoria='" + pro.getCategoria() + "', imagen='" + pro.getImagen() + "', precio=" + pro.getPrecio() + ", unidades=" + pro.getUnidades() + " where nombre='" + pro.getNombre()+"'";
        stmt.executeUpdate(sentencia);
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
        return true;

    }
}
